USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAds_Search]    Script Date: 05/25/2011 06:58:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [classified].[ClassifiedAds_Search]
	@ResourceID bigint,
	@IsRadios bit,
	@Miles int,
	@AdType tinyint,
	@Pictures bit,
	@SearchAtt searchattributes readonly,
	@SortMode tinyint,
	@Categories categories readonly,
	@AdsOnPage int,
	@StartIndex int
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @RowCnt int = @StartIndex+@AdsOnPage;
	SET ROWCOUNT @RowCnt;
	
	DECLARE @AttrCnt int;
	SELECt @AttrCnt = COUNT(*) FROM @SearchAtt
	
	
	DECLARE @mil_met float = 0.000621371;
	DECLARE @DefaultLocation geography
	SELECT @DefaultLocation = DefaultLocation FROM classified.ClassifiedResources
	WHERE ResourceID = @ResourceID
	
	
	
	DECLARE @SearchResource nvarchar(100) = ' AND ResourceID = @ResourceID '
	DECLARE @SearchRadio nvarchar(100) = ' AND Location IS NOT NULL AND @DefaultLocation.STDistance(Location)*@mil_met<@Miles '
	DECLARE @Offer nvarchar(100) = ' AND Searching = 0 '
	DECLARE @Need nvarchar(100) = ' AND Searching = 1 '
	DECLARE @SortRecent nvarchar(100) = ' Order By PostTime DESC '
	DECLARE @SortLowPrice nvarchar(100) = ' Order By Price '
	DECLARE @SortHighPrice nvarchar(100) = ' Order By Price DESC '
	DECLARE @SearchAttributes nvarchar(400) = ' AND AdID IN (SELECT c.AdID
													FROM classified.ClassifiedAdsAttributeValues c
														WHERE EXISTS(SELECT s.AttributeID, s.AttributeValue 
														FROM @SearchAtt s
														WHERE s.AttributeValue = c.AttributeValue AND s.AttributeID = c.AttributeID)
													GROUP BY c.AdID 
													HAVING COUNT(*) = @attrCnt) '
	DECLARE @sqlBegin nvarchar(1000) = 'WITH AdsOrdered AS ( '+
										'SELECT AdID, CategoryID, Title, AdText, ROW_NUMBER() OVER(';
	DECLARE @sqlEnd nvarchar(1000) = ') AS rn FROM classified.ClassifiedAds WHERE CategoryID IN (SELECT CategoryID FROM @Categories) AND ExpiryTime> GETDATE() AND Status = 0 ';									
	DECLARE @sqlFinish nvarchar(1000) = ') SELECT AdID, CategoryID, Title, AdText FROM AdsOrdered WHERE rn>@StartIndex AND rn<=@RowCnt'
	
	DECLARE @sqlCount nvarchar(1000) = 'SELECT COUNT(*) as cnt FROM classified.ClassifiedAds WHERE CategoryID IN (SELECT CategoryID FROM @Categories) AND ExpiryTime> GETDATE() AND Status = 0'
	DECLARE @groupBy nvarchar(200) = ' GROUP BY PostTime '
	--radio search
	IF (@IsRadios = 0)
		BEGIN
			SET @sqlEnd = @sqlEnd+@SearchResource;
			SET @sqlCount = @sqlCount+@SearchResource;
		END
	ELSE 
		BEGIN
			SET @sqlEnd = @sqlEnd+@SearchRadio;
			SET @sqlCount = @sqlCount+@SearchRadio;
		END
	
	--ad type
	IF (@AdType = 0)
		BEGIN
			SET @sqlEnd = @sqlEnd+@Offer;
			SET @sqlCount = @sqlCount+@Offer;
		END
	ELSE 
		IF (@AdType = 1)
			BEGIN
				SET @sqlEnd = @sqlEnd+@Need
				SET @sqlCount = @sqlCount+@Need
			END



	--pictures
	
	--attributes 
	IF (@AttrCnt>0)
		BEGIN
			SET @sqlEnd = @sqlEnd+@SearchAttributes
			SET @sqlCount = @sqlCount+@SearchAttributes
		END

	--sort mode
	IF (@SortMode = 0)
		BEGIN
			SET @sqlBegin = @sqlBegin+@SortRecent;
		END
	ELSE IF (@SortMode = 1)
		BEGIN
			SET @sqlBegin = @sqlBegin+@SortLowPrice;
		END
	ELSE 
		BEGIN
			SET @sqlBegin = @sqlBegin+@SortHighPrice;
		END
			
	DECLARE @sql nvarchar(1000)= @sqlBegin+@sqlEnd+@sqlFinish;
	
	--count of ads
	EXEC sp_executesql @sqlCount, N'@ResourceID bigint, @DefaultLocation geography, @mil_met float, @IsRadios bit, @Miles int, @AdType tinyint, @Pictures bit, @SortMode tinyint, @SearchAtt searchattributes readonly, @Categories categories readonly, @RowCnt int, @StartIndex int, @AttrCnt int',
							   @ResourceID, @DefaultLocation, @mil_met, @IsRadios, @Miles, @AdType, @Pictures, @SortMode, @SearchAtt, @Categories, @RowCnt, @StartIndex, @AttrCnt

	--ads on page	
	EXEC sp_executesql @sql, N'@ResourceID bigint, @DefaultLocation geography, @mil_met float, @IsRadios bit, @Miles int, @AdType tinyint, @Pictures bit, @SortMode tinyint, @SearchAtt searchattributes readonly, @Categories categories readonly, @RowCnt int, @StartIndex int, @AttrCnt int',
							   @ResourceID, @DefaultLocation, @mil_met, @IsRadios, @Miles, @AdType, @Pictures, @SortMode, @SearchAtt, @Categories, @RowCnt, @StartIndex, @AttrCnt
	SET ROWCOUNT 0;
END







GO


